﻿using SQLite;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace KursachApp
{
    public class FriendAsyncRepository
    {
         SQLiteAsyncConnection database;

        public FriendAsyncRepository(string databasePath)
        {
            database = new SQLiteAsyncConnection(databasePath);
            //database.CreateTableAsync<Users>().Wait();
           // database.CreateTableAsync<Notes>().Wait();
        }
        public SQLiteAsyncConnection GetDatabaseConnection()
        {
            return database;
        }
        public async Task CreateTable()
        {
            await database.CreateTableAsync<Users>();
        }

        public async Task CreateTable2()
        {
            await database.CreateTableAsync<Notes>(); 
        }

        //public async Task SaveUserAsync(Users user)
        //{
        //    await database.InsertAsync(user);
        //}

        //public async Task<bool> CheckCredentialsAsync(string username, string password)
        //{
        //    var user = await database.Table<Users>().Where(u => u.Login == username && u.Password == password).FirstOrDefaultAsync();
        //    return user != null;
        //}

        public async Task<Users> GetUserAsync(string username, string password)
        {
            return await database.Table<Users>().Where(u => u.Login == username && u.Password == password).FirstOrDefaultAsync();
        }

        public async Task<List<Users>> GetItemsAsync()
        {
            return await database.Table<Users>().ToListAsync();

        }

        public async Task<List<Notes>> GetItemsAsync2(int usid)
        {
            //return await database.Table<Notes>().ToListAsync();
            return await database.Table<Notes>().Where(s => s.MainId == usid).ToListAsync();
        }
        public async Task<List<Notes>> GetItemsAsync3()
        {
            return await database.Table<Notes>().ToListAsync();
            //return await database.Table<Notes>().Where(s => s.MainId == usid).ToListAsync();
        }

        //public async Task<List<Notes>> GetNotesForUserAsync()
        //{
        //    // Выборка записей для конкретного пользователя
        //    var userNotes = await database.Table<Notes>().Where(n => n.MainId == 2).ToListAsync();

        //    return userNotes;
        //}

        //public async Task<Users> GetItemAsync(int id)
        //{
        //    return await database.GetAsync<Users>(id);
        //}
        public async Task<int> DeleteItemAsync(Users item)
        {
            return await database.DeleteAsync(item);
        }
        public async Task<int> DeleteItemAsync2(Notes item)
        {
            return await database.DeleteAsync(item);
        }

        public async Task<List<Notes>> GetNotesWithUserAsync()
        {
            var notes = await database.Table<Notes>().ToListAsync();
            var users = await database.Table<Users>().ToListAsync();

            var notesWithUser = from note in notes
                                join user in users on note.MainId equals user.Id
                                select new Notes
                                {
                                    Id = note.Id,
                                    MainId = note.MainId,
                                    Theme = note.Theme,
                                    Content = note.Content,
                                    Dops = note.Dops,
                                    Dates = note.Dates,
                                    User = user
                                };

            return notesWithUser.ToList();
        }

        public async Task<int> SaveItemAsync(Users item)
        {
            if (item.Id != 0)
            {
                await database.UpdateAsync(item);
                return item.Id;
            }
            else
            {
                return await database.InsertAsync(item);
            }
        }
        public async Task<int> SaveItemAsync2(Notes item)
        {
            if (item.Id != 0)
            {
                await database.UpdateAsync(item);
                return item.Id;
            }
            else
            {
                return await database.InsertAsync(item);
            }
        }


    }
}
